/************************************************************************************/
/*  Program name CAPBRIMP_COMMON.sql                                                */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/* 06 JAN, 2006   NIP Karen             Add Functions                               */
/************************************************************************************/

SET DEFINE OFF;

CREATE OR REPLACE PACKAGE CAPBRIMP_COMMON
IS
	FUNCTION GetUser(
		pUserName			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetSegment(
		pCOA_ID				NUMBER,
		pSegment			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetBGID(
		pBGName				VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetOrgID(
		pOrgName			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetSOBID(
		pSOBShort_Name		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetSOBShortName(
		pOrg_ID				NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetVendorID_Name(
		pVendor_Name		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetVendorSiteID(
		pVendor_ID			NUMBER,
		pORG_ID					NUMBER,
		pVendorSiteCode		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetCOAID(
		pSOBShort_Name		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetCOAIDfromBTC(
		pBook_Type_Code		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetCOAIDfromFlexStrCode(
		pFlexStrCode	VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetPayProgramID(
		pFriendly_Name		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetLocation(
		pLoc_Name			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetAPTerm(
		pTerm_Name			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetRespID(
		pResp_Key			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetValueSetID(
		pValueSetName		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetDocSeqID(
		pDocSeqName			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetEncumTypeID(
		pEncumType			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetBudgetVerID(
		pBudgetName			VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetFNDConcPrgID(
		pConcPrgName		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetExtBankAccID(
		pSOB_ID				NUMBER,
		pBank_Name			VARCHAR2,
		pBank_AccName		VARCHAR2
	) RETURN NUMBER;

	FUNCTION GetRCVTransID(
		pRCV_OrgName		VARCHAR2,
		pRCV_RecNum			VARCHAR2,
		pRCV_Qty			NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetSOBShortNameIO(
		pOrg_ID				NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetSOBShortNameOU(
		pOrg_ID				NUMBER
	) RETURN VARCHAR2;
	
END CAPBRIMP_COMMON;
/
SET DEFINE OFF;

CREATE OR REPLACE PACKAGE BODY CAPBRIMP_COMMON
IS
/********************************************************/
	FUNCTION GetUser(
		pUserName		VARCHAR2
	) RETURN NUMBER IS
		lv_UserID		NUMBER;
	BEGIN
		IF	pUserName	IS	NOT NULL	THEN
			BEGIN
			SELECT	User_ID
			INTO	lv_UserID
			FROM	FND_USER
			WHERE	User_Name	=	pUserName;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_UserID := NULL;
			END;
		ELSE
			lv_UserID	:=	NULL;
		END IF;
		RETURN(lv_UserID);
	END GetUser;
/********************************************************/
	FUNCTION GetSegment(
		pCOA_id			NUMBER,
		pSegment		VARCHAR2
	) RETURN NUMBER IS
		lv_CCID		NUMBER;
	BEGIN
		IF	pSegment	IS	NOT NULL	THEN
			SELECT	Code_Combination_ID
			INTO	lv_CCID
			FROM	GL_CODE_COMBINATIONS
			WHERE	Chart_of_Accounts_ID	=	pCOA_id
			AND		Segment1||'.'||Segment2||'.'||Segment3||'.'||Segment4||'.'||Segment5||'.'||Segment6||'.'||Segment7||'.'||Segment8	=	pSegment;
		ELSE
			lv_CCID	:=	NULL;
		END IF;
		RETURN(lv_CCID);
	END GetSegment;
/********************************************************/
	FUNCTION GetBGID(
		pBGName		VARCHAR2
	) RETURN NUMBER IS
		lv_BGID		NUMBER;
	BEGIN
		IF	pBGName	IS	NOT NULL	THEN
			SELECT	Business_Group_ID
			INTO	lv_BGID
			FROM	PER_BUSINESS_GROUPS
			WHERE	Name		=	pBGName;
		ELSE
			lv_BGID	:=	NULL;
		END IF;
		RETURN(lv_BGID);
	END GetBGID;
/********************************************************/
	FUNCTION GetOrgID(
		pOrgName		VARCHAR2
	) RETURN NUMBER IS
		lv_OrgID		NUMBER;
	BEGIN
		IF	pOrgName	IS	NOT NULL	THEN
			BEGIN
			SELECT	Organization_ID
			INTO	lv_OrgID
			FROM	HR_All_Organization_Units
			WHERE	Name	=	pOrgName;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				lv_OrgID := NULL;
		END;
		ELSE
			lv_OrgID	:=	NULL;
		END IF;
		RETURN(lv_OrgID);
	END GetOrgID;
/********************************************************/
	FUNCTION GetSOBID(
		pSOBShort_Name		VARCHAR2
	) RETURN NUMBER IS
		lv_SOBID		NUMBER;
	BEGIN
		IF	pSOBShort_Name	IS	NOT NULL	THEN
			SELECT	Set_Of_Books_ID
			INTO	lv_SOBID
			FROM	GL_SETS_OF_BOOKS
			WHERE	Short_Name	=	pSOBShort_Name;
		ELSE
			lv_SOBID	:=	NULL;
		END IF;
		RETURN(lv_SOBID);
	END GetSOBID;
/********************************************************/
	FUNCTION GetSOBShortName(
		pOrg_ID				NUMBER
	) RETURN VARCHAR2 IS
		lv_SOB_Short_Name		VARCHAR2(20);
	BEGIN
		IF	pOrg_ID	IS	NOT NULL	THEN
			SELECT
				SOB.Short_Name
			INTO
				lv_SOB_Short_Name
			FROM
				HR_ORGANIZATION_UNITS			HOU,
				HR_ORGANIZATION_INFORMATION		HOI,
				GL_SETS_OF_BOOKS				SOB
			WHERE	HOU.Organization_ID				=	pOrg_ID
			AND		HOU.Organization_ID				=	HOI.Organization_ID
			AND		HOI.Org_Information_Context		=	'Operating Unit Information'
			AND		TO_NUMBER(HOI.Org_Information3)	=	SOB.Set_of_Books_ID;
		ELSE
			lv_SOB_Short_Name	:=	NULL;
		END IF;
		RETURN(lv_SOB_Short_Name);
	END GetSOBShortName;
/********************************************************/
	FUNCTION GetVendorID_Name(
		pVendor_Name		VARCHAR2
	) RETURN NUMBER IS
		lv_VendID		VARCHAR2(240);
	BEGIN
		IF	pVendor_Name	IS	NOT NULL	THEN
			BEGIN
			SELECT	Vendor_ID
			INTO	lv_VendID
			FROM	PO_VENDORS
			WHERE	Vendor_Name	=	pVendor_Name;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_VendID := NULL;
			END;
		ELSE
			lv_VendID	:=	NULL;
		END IF;
		RETURN(lv_VendID);
	END GetVendorID_Name;
/********************************************************/
	FUNCTION GetVendorSiteID(
		pVendor_ID		NUMBER,
		pORG_ID			NUMBER,
		pVendorSiteCode	VARCHAR2
	) RETURN NUMBER IS
		lv_VendSiteID		NUMBER;
	BEGIN
		--IF	pVendorSiteCode	IS	NOT NULL	THEN
		IF	pORG_ID	IS	NOT NULL	THEN
			BEGIN
			SELECT	Vendor_Site_ID
			INTO	lv_VendSiteID
			FROM	PO_VENDOR_SITES_ALL
			WHERE	ORG_ID			=	pORG_ID
			AND		Vendor_Site_Code	=	pVendorSiteCode
			AND		VENDOR_ID = pVendor_ID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_VendSiteID := NULL;
			END;
		ELSE
			lv_VendSiteID	:=	NULL;
		END IF;
		RETURN(lv_VendSiteID);
	END GetVendorSiteID;
/********************************************************/
	FUNCTION GetCOAID(
		pSOBShort_Name		VARCHAR2
	) RETURN NUMBER IS
		lv_COAID		NUMBER;
	BEGIN
		IF	pSOBShort_Name	IS	NOT NULL	THEN
			SELECT	Chart_of_Accounts_ID
			INTO	lv_COAID
			FROM	GL_SETS_OF_BOOKS
			WHERE	Short_Name	=	pSOBShort_Name;
		ELSE
			lv_COAID	:=	NULL;
		END IF;
		RETURN(lv_COAID);
	END GetCOAID;
/********************************************************/
	FUNCTION GetCOAIDfromBTC(
		pBook_Type_Code     VARCHAR2
	) RETURN NUMBER IS
		lv_COAID		NUMBER;
	BEGIN
		IF	pBook_Type_Code	IS	NOT NULL	THEN
			SELECT	SOB.Chart_of_Accounts_ID
			INTO	lv_COAID
			FROM
				GL_SETS_OF_BOOKS    SOB,
				FA_BOOK_CONTROLS    FBC
			WHERE	FBC.Book_Type_Code	=	pBook_Type_Code
			AND		FBC.Set_of_Books_ID	=	SOB.Set_of_Books_ID;
		ELSE
			lv_COAID	:=	NULL;
		END IF;
		RETURN(lv_COAID);
	END GetCOAIDfromBTC;
/********************************************************/
	FUNCTION GetCOAIDfromFlexStrCode(
		pFlexStrCode	VARCHAR2
	) RETURN NUMBER IS
		lv_COAID		NUMBER;
	BEGIN
		IF	pFlexStrCode	IS	NOT NULL	THEN
			SELECT	ID_Flex_Num
			INTO	lv_COAID
			FROM	FND_ID_FLEX_STRUCTURES
			WHERE	ID_Flex_Structure_Code	=	pFlexStrCode;
		ELSE
			lv_COAID	:=	NULL;
		END IF;
		RETURN(lv_COAID);
	END GetCOAIDfromFlexStrCode;
/********************************************************/
	FUNCTION GetPayProgramID(
		pFriendly_Name		VARCHAR2
	) RETURN NUMBER IS
		lv_PrgID		NUMBER;
	BEGIN
		IF	pFriendly_Name	IS	NOT NULL	THEN
			SELECT	Program_ID
			INTO	lv_PrgID
			FROM	AP_PAYMENT_PROGRAMS
			WHERE	Friendly_Name	=	pFriendly_Name;
		ELSE
			lv_PrgID	:=	NULL;
		END IF;
		RETURN(lv_PrgID);
	END GetPayProgramID;
/********************************************************/
	FUNCTION GetLocation(
		pLoc_Name			VARCHAR2
	) RETURN NUMBER IS
		lv_LocID		NUMBER;
	BEGIN
		IF	pLoc_Name	IS	NOT NULL	THEN
			SELECT	Location_ID
			INTO	lv_LocID
			FROM	HR_Locations
			WHERE	Location_Code	=	pLoc_Name;
		ELSE
			lv_LocID	:=	NULL;
		END IF;
		RETURN(lv_LocID);
	END GetLocation;
/********************************************************/
	FUNCTION GetAPTerm(
		pTerm_Name			VARCHAR2
	) RETURN NUMBER IS
		lv_TermID		NUMBER;
	BEGIN
		IF	pTerm_Name	IS	NOT NULL	THEN
			SELECT	Term_ID
			INTO	lv_TermID
			FROM	AP_Terms
			WHERE	Name	=	pTerm_Name;
		ELSE
			lv_TermID	:=	NULL;
		END IF;
		RETURN(lv_TermID);
	END GetAPTerm;
/********************************************************/
	FUNCTION GetRespID(
		pResp_Key			VARCHAR2
	) RETURN NUMBER IS
		lv_RespID		NUMBER;
	BEGIN
		IF	pResp_Key	IS	NOT NULL	THEN
			SELECT	Responsibility_ID
			INTO	lv_RespID
			FROM	FND_RESPONSIBILITY_VL
			WHERE	Responsibility_Key	=	pResp_Key;
		ELSE
			lv_RespID	:=	NULL;
		END IF;
		RETURN(lv_RespID);
	END GetRespID;
/********************************************************/
	FUNCTION GetValueSetID(
		pValueSetName		VARCHAR2
	) RETURN NUMBER IS
		lv_VSetID		NUMBER;
	BEGIN
		IF	pValueSetName	IS	NOT NULL	THEN
			SELECT	Flex_Value_Set_ID
			INTO	lv_VSetID
			FROM	FND_FLEX_VALUE_SETS
			WHERE	Flex_Value_Set_Name	=	pValueSetName;
		ELSE
			lv_VSetID	:=	NULL;
		END IF;
		RETURN(lv_VSetID);
	END GetValueSetID;
/********************************************************/
	FUNCTION GetDocSeqID(
		pDocSeqName		VARCHAR2
	) RETURN NUMBER IS
		lv_Doc_Seq_ID		NUMBER;
	BEGIN
		IF	pDocSeqName	IS	NOT NULL	THEN
			SELECT	DOC_SEQUENCE_ID
			INTO	lv_Doc_Seq_ID
			FROM	FND_DOCUMENT_SEQUENCES
			WHERE	NAME	=	pDocSeqName;
		ELSE
			lv_Doc_Seq_ID	:=	NULL;
		END IF;
		RETURN(lv_Doc_Seq_ID);
	END GetDocSeqID;
/********************************************************/
	FUNCTION GetEncumTypeID(
		pEncumType		VARCHAR2
	) RETURN NUMBER IS
		lv_Encum_Type_ID		NUMBER;
	BEGIN
		IF	pEncumType	IS	NOT NULL	THEN
			SELECT	ENCUMBRANCE_TYPE_ID
			INTO	lv_Encum_Type_ID
			FROM	GL_ENCUMBRANCE_TYPES
			WHERE	ENCUMBRANCE_TYPE	=	pEncumType;
		ELSE
			lv_Encum_Type_ID	:=	NULL;
		END IF;
		RETURN(lv_Encum_Type_ID);
	END GetEncumTypeID;
/********************************************************/
	FUNCTION GetBudgetVerID(
		pBudgetName		VARCHAR2
	) RETURN NUMBER IS
		lv_Budget_Ver_ID		NUMBER;
	BEGIN
		IF	pBudgetName	IS	NOT NULL	THEN
			SELECT	BUDGET_VERSION_ID
			INTO	lv_Budget_Ver_ID
			FROM	GL_BUDGET_VERSIONS
			WHERE	BUDGET_NAME	=	pBudgetName;
		ELSE
			lv_Budget_Ver_ID	:=	NULL;
		END IF;
		RETURN(lv_Budget_Ver_ID);
	END GetBudgetVerID;
/********************************************************/
	FUNCTION GetFNDConcPrgID(
		pConcPrgName		VARCHAR2
	) RETURN NUMBER IS
		lv_ConcPrg_ID			NUMBER;
	BEGIN
		IF	pConcPrgName	IS	NOT NULL	THEN
			SELECT	Concurrent_Program_ID
			INTO	lv_ConcPrg_ID
			FROM	FND_CONCURRENT_PROGRAMS
			WHERE	Concurrent_Program_Name	=	pConcPrgName;
		ELSE
			lv_ConcPrg_ID	:=	NULL;
		END IF;
		RETURN(lv_ConcPrg_ID);
	END GetFNDConcPrgID;
/********************************************************/
	FUNCTION GetExtBankAccID(
		pSOB_ID				NUMBER,
		pBank_Name			VARCHAR2,
		pBank_AccName		VARCHAR2
	) RETURN NUMBER IS
		lv_ExtBankID	NUMBER;
	BEGIN
		IF	pBank_Name		IS	NOT NULL	AND
			pBank_AccName	IS	NOT NULL	THEN
			SELECT
				A.Bank_Account_ID
			INTO
				lv_ExtBankID
			FROM
				AP_BANK_ACCOUNTS_ALL	A,
				AP_BANK_BRANCHES		B
			WHERE	A.Set_of_Books_ID	=	pSOB_ID
			AND		A.Bank_Branch_ID	=	B.Bank_Branch_ID
			AND		A.Account_Type		=	'SUPPLIER'
			AND		B.Bank_Name			=	pBank_Name
			AND		A.Bank_Account_Name	=	pBank_AccName;
		ELSE
			lv_ExtBankID	:=	NULL;
		END IF;
		RETURN(lv_ExtBankID);
	END GetExtBankAccID;
/********************************************************/
	FUNCTION GetRCVTransID(
		pRCV_OrgName		VARCHAR2,
		pRCV_RecNum			VARCHAR2,
		pRCV_Qty			NUMBER
	) RETURN NUMBER IS
		lv_RCVTrans_ID	NUMBER;
	BEGIN
		IF	pRCV_OrgName	IS	NOT NULL	AND
			pRCV_RecNum		IS	NOT NULL	AND
			pRCV_Qty		IS	NOT NULL	THEN
			SELECT
				A.Transaction_ID
			INTO
				lv_RCVTrans_ID
			FROM
				RCV_TRANSACTIONS			A,
				RCV_SHIPMENT_HEADERS		B,
				HR_ALL_ORGANIZATION_UNITS	C
			WHERE	A.Transaction_Type		=	'RECEIVE'
			AND		A.Organization_ID		=	B.Ship_To_Org_ID
			AND		A.Shipment_Header_ID	=	B.Shipment_Header_ID
			AND		B.Ship_To_Org_ID		=	C.Organization_ID
			AND		C.Name					=	pRCV_OrgName
			AND		B.Receipt_Num			=	pRCV_RecNum
			AND		A.Quantity				=	pRCV_Qty;
		ELSE
			lv_RCVTrans_ID	:=	NULL;
		END IF;
		RETURN(lv_RCVTrans_ID);
	END GetRCVTransID;
/********************************************************/
/********************************************************/
FUNCTION GetSOBShortNameIO(
		pOrg_ID				NUMBER
	) RETURN VARCHAR2 IS
		lv_SOB_Short_Name		VARCHAR2(20);
	BEGIN
		IF	pOrg_ID	IS	NOT NULL	THEN
			SELECT
				SOB.Short_Name
			INTO
				lv_SOB_Short_Name
			FROM
				HR_ALL_ORGANIZATION_UNITS			HOU,
				HR_ORGANIZATION_INFORMATION		HOI,
				GL_SETS_OF_BOOKS				SOB
			WHERE	HOU.Organization_ID				=	pOrg_ID
			AND		HOU.Organization_ID				=	HOI.Organization_ID
			AND		HOI.Org_Information_Context		=	'Accounting Information'
			AND		TO_NUMBER(HOI.Org_Information1)	=	SOB.Set_of_Books_ID;
		ELSE
			lv_SOB_Short_Name	:=	NULL;
		END IF;
		RETURN(lv_SOB_Short_Name);
	END GetSOBShortNameIO;
/********************************************************/
FUNCTION GetSOBShortNameOU(
		pOrg_ID				NUMBER
	) RETURN VARCHAR2 IS
		lv_SOB_Short_Name		VARCHAR2(20);
	BEGIN
		IF	pOrg_ID	IS	NOT NULL	THEN
			SELECT
				SOB.Short_Name
			INTO
				lv_SOB_Short_Name
			FROM
				HR_ALL_ORGANIZATION_UNITS			HOU,
				HR_ORGANIZATION_INFORMATION		HOI,
				GL_SETS_OF_BOOKS				SOB
			WHERE	HOU.Organization_ID				=	pOrg_ID
			AND		HOU.Organization_ID				=	HOI.Organization_ID
			AND		HOI.Org_Information_Context		=	'Operating Unit Information'
			AND		TO_NUMBER(HOI.Org_Information3)	=	SOB.Set_of_Books_ID;
		ELSE
			lv_SOB_Short_Name	:=	NULL;
		END IF;
		RETURN(lv_SOB_Short_Name);
	END GetSOBShortNameOU;
/********************************************************/
END CAPBRIMP_COMMON;
/
